﻿Imports System.Data.OleDb
Imports System.IO
Imports System.IO.File
Imports System.Data
Imports System.Data.SqlClient
Public Class 扫码模板管理

    Private Sub 扫码模板管理_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Custom_alarm_editing()
    End Sub

    ''' 利用界面上的查询按钮
    ''' 利用SQL语句查询数据库内容，查询数据库中的所有数据，并将数据库的内容写入到datagridview控件
    ''' 该函数是操作报警记录报表
    ''' <param name="path"></param>
    ''' <remarks></remarks>
    Private Sub Custom_alarm_editing()
        Try
            Dim sql As String = "select * from Base_SN"
            ' Dim sql1 As String = "Base_SN.accdb"
            Dim ds As DataSet = SearchMysql(sql)
            DataGridView4.DataSource = ds.Tables(0)
            Me.DataGridView4.Columns(0).Width = 80
            Me.DataGridView4.Columns(1).Width = 180
            Me.DataGridView4.Columns(2).Width = 180
            ' Me.DataGridView4.Columns(3).Width = 120
            Me.DataGridView4.Columns(0).SortMode = DataGridViewColumnSortMode.NotSortable                       '禁止列排序
            Me.DataGridView4.Columns(1).SortMode = DataGridViewColumnSortMode.NotSortable                       '禁止列排序
            Me.DataGridView4.Columns(2).SortMode = DataGridViewColumnSortMode.NotSortable                       '禁止列排序
            ' Me.DataGridView4.Columns(3).SortMode = DataGridViewColumnSortMode.NotSortable                       '禁止列排序
            DataGridView4.DefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomCenter                '字体居中
            DataGridView4.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomCenter   '标题居中
            DataGridView4.AllowUserToAddRows = False                                                            '清空最后空白行
            Dim filepath = DebugPath & "\Tool\"
            Dim JQSN As Boolean = Exists(filepath & "\JQGZ.txt")
            If JQSN Then
                Dim startDly1 As Integer = FreeFile()
                FileOpen(startDly1, filepath & "\JQGZ.txt", OpenMode.Random)
                FileGet(startDly1, JQStart)
                FileGet(startDly1, JQLength)
                FileClose(startDly1)
                TextBox_Start.Text = JQStart
                TextBox_Length.Text = JQLength
            End If
        Catch ex As Exception
            MsgBox(ex.Message.ToString & vbCrLf & ex.StackTrace.ToString)
        End Try
    End Sub


    ''' 自定义报警新建添加
    ''' 
    ''' 
    ''' <param name="path"></param>
    ''' <remarks></remarks>
    Private Sub Custom_ADD_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Custom_ADD.Click
        Try
            If Custom_English.Text <> "" And Custom_Cause.Text <> "" Then
                Dim sql As String = "insert into Base_SN(SN,MODE) values(" & "'" & Custom_English.Text & "'" & "," & "'" & Custom_Cause.Text & "'" & ")"
                'Dim sql1 As String = "Base_SN.accdb"
                'AppendInfoToTextBox("信息写入")
                HandleMysql(sql)
                Custom_alarm_editing()
                Custom_English.Text = ""
                Custom_Chinese.Text = ""
                Custom_Cause.Text = ""
            Else
                MsgBox("必要信息为空，添加失败。")
            End If
        Catch ex As Exception
            'AppendInfoToTextBox(ex.Message.ToString)
            MsgBox(ex.Message.ToString & vbCrLf & ex.StackTrace.ToString)
            'Error_report_storage(Format(DateAndTime.Now, "yyyy-MM-dd HH:mm:ss"), Todaycount.PassCount + 1, Chanpin, ex.Message.ToString, "待分析", "咨询生产厂商", "软件故障")   '''信息写入数据库中
        End Try
    End Sub

    ''' 设置ID号输入框只能输入数字
    ''' 
    ''' <param name="path"></param>
    ''' <remarks></remarks>
    Private Sub Custom_ID_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles Custom_ID.KeyPress
        If Char.IsDigit(e.KeyChar) Or e.KeyChar = Chr(8) Then
            e.Handled = False
        Else
            e.Handled = True
        End If
    End Sub


    ''' 自定义报警根据ID号删除
    ''' 
    ''' 
    ''' <param name="path"></param>
    ''' <remarks></remarks>
    Private Sub Custom_deleting_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Custom_deleting.Click

        Try
            If Custom_ID.Text <> "" Then
                'Dim cnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= Tool\Base_SN.accdb;Persist Security Info=False"
                Dim sql As String = "delete from Base_SN where ID =" & CInt(Custom_ID.Text)
                HandleMysql(sql)
                Custom_alarm_editing()
            Else
                MsgBox("ID号不能为空。")
            End If
        Catch ex As Exception
            'AppendInfoToTextBox(ex.Message.ToString)
            MsgBox(ex.Message.ToString & vbCrLf & ex.StackTrace.ToString)
            'Error_report_storage(Format(DateAndTime.Now, "yyyy-MM-dd HH:mm:ss"), Todaycount.PassCount + 1, Chanpin, ex.Message.ToString, "待分析", "咨询生产厂商", "软件故障")   '''信息写入数据库中
        End Try

    End Sub
    ''' 自定义报警根据ID号修改
    ''' 
    ''' 
    ''' <param name="path"></param>
    ''' <remarks></remarks>
    Private Sub Custom_modification_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Custom_modification.Click
        Try
            If Custom_English.Text <> "" And Custom_Cause.Text <> "" And Custom_ID.Text <> "" Then
                Dim sql As String = "update Base_SN set SN ='" & Custom_English.Text & "'" & "," & "MODE ='" & Custom_Cause.Text & "'" & "where ID =" & CInt(Custom_ID.Text)
                'Dim sql As String = "update 用户定义报警 set 英文报警 = '" & Custom_English.Text & "'where ID =" & CInt(Custom_ID.Text)
                ' Dim sql1 As String = "Base_SN.accdb"
                'AppendInfoToTextBox("信息写入")
                HandleMysql(sql)
                Custom_alarm_editing()
                Custom_English.Text = ""
                Custom_Chinese.Text = ""
                Custom_Cause.Text = ""
                Custom_ID.Text = ""
            Else
                MsgBox("必要信息为空，修改失败。")
            End If
        Catch ex As Exception
            'AppendInfoToTextBox(ex.Message.ToString)
            MsgBox(ex.Message.ToString & vbCrLf & ex.StackTrace.ToString)
            'Error_report_storage(Format(DateAndTime.Now, "yyyy-MM-dd HH:mm:ss"), Todaycount.PassCount + 1, Chanpin, ex.Message.ToString, "待分析", "咨询生产厂商", "软件故障")   '''信息写入数据库中
        End Try
    End Sub

    Private Sub DataGridView4_Click(sender As System.Object, e As System.EventArgs) Handles DataGridView4.Click
        Try
            Dim q As Integer = DataGridView4.CurrentRow.Index
            Dim l As String = DataGridView4(0, q).Value.ToString
            Dim sql As String = "Select * from Base_SN where ID = " & l
            ' Dim sql1 As String = "Base_SN.accdb"
            Dim ds As DataSet = SearchMysql(sql)
            'DataGridView4.DataSource = ds.Tables(0)
            If ds.Tables(0).Rows.Count > 0 Then
                Custom_ID.Text = ds.Tables(0).Rows(0)(0).ToString
                Custom_English.Text = ds.Tables(0).Rows(0)(1).ToString
                'Custom_Chinese.Text = ds.Tables(0).Rows(0)(2).ToString
                Custom_Cause.Text = ds.Tables(0).Rows(0)(2).ToString
            End If
        Catch ex As Exception
        End Try
    End Sub

    Public Function query(ByVal sn As String) As String
        Dim templatename As String = ""
        Try
            If sn <> "" Then
                Dim ssn As String = sn.Substring(JQStart, JQLength)
                Dim sql1 As String = "Base_SN.accdb"
                Dim sql As String = "select * from Base_SN where SN =" & "'" & ssn & "'"
                Dim ds As DataSet = SearchMysql(sql)
                If ds.Tables(0).Rows.Count > 0 Then
                    'For i = 0 To ds.Tables(0).Rows.Count - 1
                    'If ds.Tables(0).Rows(i)("SN").ToString = ssn Then
                    templatename = ds.Tables(0).Rows(0)("MODE").ToString
                    'End If
                    'Next
                Else
                    'FrmMain.AppendInfoToTextBox("未编辑对应的模板")
                End If
            End If

            Return templatename
        Catch ex As Exception
            MsgBox(ex.Message.ToString & vbCrLf & ex.StackTrace.ToString)
            Return templatename
        End Try
    End Function
    'Public Function query1(model As String) As String
    '    Try
    '        Dim sql1 As String = "Base_SN.accdb"
    '        Dim sql As String = "select * from domino where 机种 =" & "'" & model & "'"
    '        Dim templatename As String = ""

    '        Dim ds As DataSet = SearchDB4(sql, sql1)
    '        If ds.Tables(0).Rows.Count > 0 Then
    '            For i = 0 To ds.Tables(0).Rows.Count
    '                If ds.Tables(0).Rows(i)("机种").ToString = model Then
    '                    templatename = ds.Tables(0).Rows(i)("模板名").ToString
    '                End If
    '                If templatename <> "" Then
    '                    i = ds.Tables(0).Rows.Count + 1
    '                End If
    '            Next
    '        Else
    '            'FrmMain.AppendInfoToTextBox("未编辑对应的模板")
    '        End If
    '        Return templatename
    '    Catch ex As Exception
    '        MsgBox(ex.Message.ToString & vbCrLf & ex.StackTrace.ToString)
    '    End Try
    'End Function

    '引用excel表格内容
    ''' <summary>
    ''' 将DataGridView的数据保存到数据库
    ''' <remarks>dgv作为datagridview控件缩写，当做一个参数</remarks>
    ''' 
    Public Function Saveform(ByVal dgv As DataGridView) As Boolean
        Try
            Dim sql As String
            Dim cnStr2 As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= Tool\Base_SN.accdb;Persist Security Info=False"
            Dim cnN As OleDbConnection = New OleDbConnection(cnStr2)
            Dim dc2 As OleDbCommand
            cnN.Open() '打开数据库
            If dgv.Rows.Count > 1 Then
                '导出表头  
                Dim i As Integer
                '利用循环嵌套，将datagridview内容放到导出为excel表格
                '这里有两个循环i表示行循环，j为列循环，excel先从行开始循环，excel需要从列开始循环
                For i = 0 To dgv.Rows.Count - 1   ' 行
                    Dim s1 As String = dgv(1, i).Value.ToString
                    Dim s2 As String = dgv(2, i).Value.ToString
                    Dim s3 As String = dgv(3, i).Value.ToString
                    'For j = 0 To dgv.ColumnCount - 1  ' 列
                    sql = "update domino set 机种=" & "'" & s1 & "'" & "," & "颜色=" & "'" & s2 & "'" & "," & "模板名=" & "'" & s3 & "'" & " where ID = " & dgv(0, i).Value.ToString
                    dc2 = New OleDbCommand(sql, cnN)
                    dc2.ExecuteNonQuery()
                    'sql = "update domino set 颜色=" & s2 & " where ID = " & dgv(0, i).Value.ToString & " "
                    'dc2 = New OleDbCommand(sql, cnN)
                    'dc2.ExecuteNonQuery()
                    'sql = "update domino set 模板名=" & s3 & " where ID = " & dgv(0, i).Value.ToString & " "
                    'dc2 = New OleDbCommand(sql, cnN)
                    'dc2.ExecuteNonQuery()
                    'Next
                Next
                MsgBox("保存完成")

            Else
                MsgBox("提示：表格中没有数据记录，不能进行保存！")
            End If
            Return True
            cnN.Close() '关闭数据库

        Catch ex As Exception
            MsgBox(ex.Message.ToString & vbCrLf & ex.StackTrace.ToString)
        End Try
    End Function

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Saveform(DataGridView4)
        Custom_alarm_editing()
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Try
            JQStart = TextBox_Start.Text.ToString
            JQLength = Val(TextBox_Length.Text)
            Dim Para_num As Integer = FreeFile()
            Dim filepath = DebugPath & "\Tool\" '& Chanpin & "\"
            'Dim OpenPsetsetting As Boolean = Exists(filepath & "Psetsetting.txt")
            FileOpen(Para_num, filepath & "JQGZ.txt", OpenMode.Random)
            FilePut(Para_num, JQStart)
            FilePut(Para_num, JQLength)
            FileClose(Para_num)
            MsgBox("保存成功！")
        Catch ex As Exception
            MsgBox(ex.Message.ToString & vbCrLf & ex.StackTrace.ToString)
        End Try
       
    End Sub
End Class